﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Collections;
using DTcms.Common;

namespace DTcms.Web.admin.erp
{
    public partial class erpcaiji : DTcms.Web.UI.ManagePage
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }


        protected void btnUp_Click(object sender, EventArgs e)
        {
            bool b = Upload(fuExcel);  // 上传excel文件 
            if (!b)
            {
                return;
            }
            string name = fuExcel.FileName;
            string filepath = Server.MapPath("~/upload/") + name;
            DataSet ds = ExcelDataSource(filepath, ExcelSheetName(filepath)[0].ToString());
            //GridView1.DataSource = ds;
            //GridView1.DataBind();
            UpdataERP(ds);
        }

        protected void UpdataERP(DataSet _ds)
        {
            BLL.dt_erp bll = new BLL.dt_erp();
            int tempnum = 0;
                
            if (_ds.Tables!=null)
            {
                foreach (DataRow item in _ds.Tables[0].Rows)
                {
                    if (item["公司名称"].ToString() != "")
                    {
                        if (bll.GetList("epr_name='" + item["公司名称"].ToString() + "' or erp_phonenumber='" + item["手机"].ToString() + "'").Tables[0].Rows.Count == 0)
                        {
                            if (DoAddERP(item))
                            {
                                tempnum++;
                            }
                        }
                    }
                    
                }
            }
            lblMes.Text = "共增加了"+tempnum.ToString()+"条数据！";
            
        }

        #region 增加操作=================================
        private bool DoAddERP(DataRow _dr)
        {

            Model.dt_erp model = new Model.dt_erp();
            BLL.dt_erp bll = new BLL.dt_erp();



            model.epr_name = _dr["公司名称"].ToString();
            model.erp_Add = _dr["地址"].ToString();
            model.erp_bak1 = 0;//默认值为0  表示该条记录没有电话访问
            model.erp_bak2 = 0;
            model.erp_bak3 = "暂无电话访问结果！";
            model.erp_bak4 = _dr["QQ"].ToString();
            model.erp_bak5 = DateTime.Now;
            model.erp_contacts = _dr["联系人"].ToString();
            model.erp_email = _dr["邮箱"].ToString();
            model.erp_number = _dr["电话"].ToString();
            model.erp_phonenumber = _dr["手机"].ToString();
            model.erp_sources = 2;//2表示数据来源于一比多
            model.erp_web = _dr["网址"].ToString(); ;

            model.user_addtime = DateTime.Now;

            Model.manager managermodel = GetAdminInfo();

            model.user_id = managermodel.id;
            model.user_lasttime = DateTime.Now;


            return bll.Add(model);
        }
        #endregion

        //上传文件方法  
        private bool Upload(FileUpload myFileUpload)
        {
            bool flag = false;
            //是否允许上载  
            bool fileAllow = false;
            //设定允许上载的扩展文件名类型  
            string[] allowExtensions = { ".xls" };

            //取得网站根目录路径  
            string path = HttpContext.Current.Request.MapPath("~/upload/");
            if (myFileUpload.HasFile)
            {
                string fileExtension = System.IO.Path.GetExtension(myFileUpload.FileName).ToLower();
                for (int i = 0; i < allowExtensions.Length; i++)
                {
                    if (fileExtension == allowExtensions[i])
                    {
                        fileAllow = true;
                    }
                }

                if (fileAllow)
                {
                    try
                    {
                        //存储文件到文件夹 
                        myFileUpload.SaveAs(path + myFileUpload.FileName);
                        lblMes.Text = "文件导入成功";
                        flag = true;
                    }
                    catch (Exception ex)
                    {
                        lblMes.Text += ex.Message;
                        flag = false;
                    }
                }
                else
                {
                    lblMes.Text = "不允许上载：" + myFileUpload.PostedFile.FileName + "，只能上传xls的文件，请检查！";
                    flag = false;
                }
            }
            else
            {
                lblMes.Text = "请选择要导入的excel文件!";
                flag = false;
            }
            return flag;
        }

        /// <summary>
        /// 该方法实现从Excel中导出数据到DataSet中
        /// </summary>
        /// <param name="filepath">Excel文件的绝对路径</param>
        /// <param name="sheetname">excel文件中的表名</param>
        /// <returns></returns>
        public DataSet ExcelDataSource(string filepath, string sheetname)
        {
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
            DataSet ds = new DataSet();
            oada.Fill(ds);
            conn.Close();
            return ds;
        }

        //获得Excel中的所有sheetname。
        public ArrayList ExcelSheetName(string filepath)
        {
            ArrayList al = new ArrayList();
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable sheetNames = conn.GetOleDbSchemaTable
            (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            conn.Close();
            foreach (DataRow dr in sheetNames.Rows)
            {
                al.Add(dr[2]);
            }
            return al;
        }

    }
}